Imports DAL
Imports MyClasses
Imports System.Data.SqlClient
Imports System.Text

Public Class DSProc

    Public Shared Function SFProc1()
        Dim strsql As String = ""
        Try
            strsql = " Select Distinct Col003 From Product"
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSProc", "SFProc1")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetMFctrID()
        Dim strsql As String = ""
        Try
            strsql = " SELECT MAX(MFctrID) FROM mManufacturer"
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSProc", "GetMFctrID")
            Return Nothing
        End Try
    End Function

    Public Shared Function SFproc2(ByVal MfctrID As Integer, ByVal CmpName As String)
        Dim strsql As String = String.Empty
        Dim CmpShort As String = String.Empty
        If CmpName.Length > 6 Then
            CmpShort = CmpName.Substring(0, 6)
        End If
        Try
            strsql = " Select Distinct 'Insert Into MManufacturer (mfctrid,seqmfctrid,personid,mfctrname,mfctrshortname,description,status,lu,ld)" & _
                     " Values(' + '" & MfctrID & "' + ',' + '''M' + '" & MfctrID & "' + '''' + ',0' + ',' + '''" & CmpName & "'',' + '''" & CmpShort & "'',' + ''' '',' + '''A'',' + '''" & CCommon.GetUserID & "'',' + '''" & Date.Now & "'')' AS X" & _
                     " From Product Where Col003 = '" & CmpName & "'"
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSProc", "SFproc2")
            Return Nothing
        End Try
    End Function

    Public Shared Function SFdocnum(ByVal MfctrID As Integer)
        Dim strsql As String = ""
        Try
            strsql = " UPDATE Sdocnum SET Lastnum = " & MfctrID & " WHERE doctype = 'MFR' "
            Return DbHelper.ExecuteNonQuery(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSProc", "SFdocnum")
            Return Nothing
        End Try
    End Function

    Public Shared Function SIProc1()
        Dim strsql As String = ""
        Try
            strsql = " Select Col001,Col004,Col005,Col006,Col014 From Product"
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSProc", "SIProc1")
            Return Nothing
        End Try
    End Function

    Public Shared Function SIproc2(ByVal ItemID As Integer, ByVal SequentialID As String, _
    ByVal ItemName As String, ByVal Category As String, ByVal GenericName As String, _
    ByVal ItemPurP As Integer, ByVal PurUnit As String, ByVal Schedule As String)
        Dim strsql As String = ""
        Try
            strsql = " Select Distinct 'Insert Into MpItem (ItemID,SeqItemID,ItemName," & _
                     " ItemShortName,ItemType,ItemCatID,ItemForm,Grade,MfctrID,CompanyID," & _
                     " GenericName,ItemUnit,PurPack,ItemsPerPack,PurUnit,LocationId," & _
                     " SellLoose,SellUnit,LastPurPrice,PurPrice,SalePrice,Barcode," & _
                     " Maxdiscount,OpeningStock,MinQty,MaxQty,MinReorderLevel," & _
                     " MaxReorderQty,Action1,Action2,Action3,VendorID1,VendorID2," & _
                     " VendorID3,ScheduleDrug,Remark,Status,LU,LD,MarginComm,ELStock)" & _
                     " Values(' + '" & ItemID & "' + ',' + '''" & SequentialID & "'','" & _
                     " + '''" & ItemName & "'',' + '''" & ItemName & "'',' + '''" & Category & "'','" & _
                     " + ''' '',' + ''' '',' + ''' '',' + ''' '',' + ''' '','" & _
                     " + '''" & GenericName & "'',' + ''' '',' + ''' '','" & _
                     " + '" & ItemPurP & "' + ',' + '''" & PurUnit & "'','" & _
                     " + ''' '',' + ''' '',' + ''' '',' + ''' '',' + ''' '',' + ''' '',' + ''' '','" & _
                     " + ''' '',' + ''' '',' + ''' '',' + ''' '',' + ''' '','" & _
                     " + ''' '',' + ''' '',' + ''' '',' + ''' '',' + ''' '',' + ''' '','" & _
                     " + ''' '',' + '''" & Schedule & "'',' + ''' '','" & _
                     " + '''A'',' + '''" & CCommon.GetUserID & "'',' + '''" & Date.Now & "'',' + ''' '',' + ''' '')' AS X" & _
                     " From Product Where Col001 = '" & ItemName & "'"
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSProc", "SIproc2")
            Return Nothing
        End Try
    End Function

    Public Shared Function SIdocnum(ByVal ItemID As Integer)
        Dim strsql As String = ""
        Try
            strsql = " UPDATE Sdocnum SET Lastnum = " & ItemID & " WHERE doctype = 'ITEM' "
            Return DbHelper.ExecuteNonQuery(strsql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSProc", "SIdocnum")
            Return Nothing
        End Try
    End Function


End Class
